﻿using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Text;
using System.Web;

namespace HelloWorldBll.Helper
{
    /// <summary>
    /// 导出excel表格
    /// </summary>
    public class ExcelHelper
    {
        public string DataTableToCsv(DataTable table)
        {
            var result = new StringBuilder();
            for (int i = 0; i < table.Columns.Count; i++)
            {
                result.Append(table.Columns[i].ColumnName);
                result.Append(i == table.Columns.Count - 1 ? "\n" : ",");

            }
            foreach (DataRow row in table.Rows)
            {
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    result.Append("\"" + row[i].ToString().Replace("\"", "\"\"") + "\"");
                    result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
                }
            }
            return result.ToString();
        }


        /// <summary>
        ///datatable转csv文件下载
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="filename"></param>
        public void ExportToSvc(System.Data.DataTable dt, string filename)
        {
            try
            {
                string strFileName = System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);

                string data = DataTableToCsv(dt);

                string temp = string.Format("attachment;filename={0}", strFileName);
                // Response.ClearHeaders();
                HttpContext.Current.Response.AppendHeader("Content-disposition", temp);
                HttpContext.Current.Response.Write(data);
                HttpContext.Current.Response.End();
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary> 
        /// 读取Excel文档 
        /// </summary> 
        /// <param name="Path">文件名称</param> 
        /// <returns>返回一个数据集</returns> 
        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            DataSet ds = null;
            strExcel = "select * from [Sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            conn.Close();
            return ds;
        }
        public static readonly string saveFilePath = System.Configuration.ConfigurationManager.AppSettings["SaveFilePath"].ToString();

        public ExcelHelper()
        {
            //
            // TOD Add constructor logic here
            //
        }
        public void getExcelFile(DataTable dtData, DataTable dtHeader, string FileName, System.Web.UI.Page Page)
        {

            HttpResponse resp = Page.Response;

            resp.Clear();
            resp.Buffer = true;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls");
            resp.AddHeader("Content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName + ".xls", Encoding.UTF8));
            resp.ContentType = "application/ms-excel";
            string colHeaders = "", ls_item = "";
            int i = 0;

            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            foreach (DataRow row in dtHeader.Rows)
                colHeaders += ReplaceEnter(row[0].ToString()) + "\t";
            colHeaders += "\n";

            //向HTTP输出流中写入取得的数据信息 
            resp.Write(colHeaders);

            //逐行处理数据 
            foreach (DataRow row in dtData.Rows)
            {
                //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                for (i = 0; i < dtData.Columns.Count; i++)
                {
                    if (dtData.Columns[i].ColumnName.Trim().ToLower() == "bookingdate")
                    {
                        ls_item += " " + DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss") + "\t";
                    }
                    else if (dtData.Columns[i].ColumnName.Trim().ToLower() == "cancellationdate")
                    {
                        if (row[i].ToString().Trim().Length > 0)
                            ls_item += " " + DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss") + "\t";
                        else
                            ls_item += " " + "\t";
                    }
                    else if (dtData.Columns[i].ColumnName.Trim().ToLower() == "arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower() == "departuredate")
                    {
                        ls_item += " " + DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd") + "\t";
                    }
                    else
                        ls_item += ReplaceEnter(row[i].ToString()) + "\t";
                }
                ls_item += "\n";
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();
        }

        //！！注意：getExcelFile函数是专用函数，要调用通用函数，请使用getExcelFileCommon()
        public void getExcelFile(DataTable dtData, string FileName, System.Web.UI.Page Page)
        {
            HttpResponse resp = Page.Response;
            resp.ClearContent();
            resp.HeaderEncoding = Encoding.UTF8;
            //resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //设置内容编码以防乱码
            //resp.Charset = "utf-8";
            //resp.AddHeader("Content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName + ".xls", Encoding.UTF8));
            //resp.ContentType = "application/ms-excel";


            resp.Buffer = true;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls");
            resp.AddHeader("Content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName + ".xls", Encoding.UTF8));

            resp.ContentType = "application/ms-excel";
            string colHeaders = "", ls_item = "";
            int i = 0;


            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < dtData.Columns.Count; i++)
                colHeaders += ReplaceEnter(dtData.Columns[i].Caption.ToString()) + "\t";
            colHeaders += "\n";

            //向HTTP输出流中写入取得的数据信息 
            resp.Write(colHeaders);

            //逐行处理数据 
            foreach (DataRow row in dtData.Rows)
            {
                //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                for (i = 0; i < dtData.Columns.Count; i++)
                {
                    if (dtData.Columns[i].ColumnName.Trim().ToLower() == "bookingdate")
                    {
                        ls_item += " " + DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss") + "\t";
                    }
                    else if (dtData.Columns[i].ColumnName.Trim().ToLower() == "cancellationdate")
                    {
                        if (row[i].ToString().Trim().Length > 0)
                            ls_item += " " + DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd hh:mm:ss") + "\t";
                        else
                            ls_item += " " + "\t";
                    }
                    else if (dtData.Columns[i].ColumnName.Trim().ToLower() == "arrivaldate" || dtData.Columns[i].ColumnName.Trim().ToLower() == "departuredate")
                    {
                        ls_item += " " + DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd") + "\t";
                    }
                    else
                        ls_item += ReplaceEnter(row[i].ToString()) + "\t";
                }
                ls_item += "\n";
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();
        }
        /// <summary>
        /// 通用excel报表生成函数
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="FileName"></param>
        /// <param name="Page"></param>
        /// <param name="sArrDateCloumn">日期列的列名数组</param>
        public void getExcelFileCommon(DataTable dtData, string FileName, System.Web.UI.Page Page, string[] sArrDateCloumn)
        {
            #region 固定代码
            HttpResponse resp = Page.Response;

            resp.Clear();
            resp.Buffer = true;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls");
            resp.AddHeader("Content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName + ".xls", Encoding.UTF8));
            resp.ContentType = "application/ms-excel";
            string colHeaders = "", ls_item = "";
            int i = 0;


            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            for (i = 0; i < dtData.Columns.Count; i++)
                colHeaders += ReplaceEnter(dtData.Columns[i].Caption.ToString()) + "\t";
            colHeaders += "\n";

            //向HTTP输出流中写入取得的数据信息 
            resp.Write(colHeaders);
            #endregion

            //逐行处理数据 
            bool bFinded = false;
            foreach (DataRow row in dtData.Rows)
            {
                //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                for (i = 0; i < dtData.Columns.Count; i++)
                {
                    bFinded = false;
                    if (sArrDateCloumn.Length == 0)
                    {
                        ls_item += ReplaceEnter(row[i].ToString()) + "\t";
                    }
                    else
                    {
                        foreach (string ss in sArrDateCloumn)//对传入的每个日期列名数组元素
                        {
                            if (dtData.Columns[i].ColumnName.Trim().ToLower() == ss)
                            {
                                if (row[i].ToString().Trim() != "")
                                {
                                    ls_item += " " + DateTime.Parse(ReplaceEnter(row[i].ToString()).Trim()).ToString("yyyy-MM-dd") + "\t";
                                    bFinded = true;
                                    break;
                                }
                            }
                        }
                        if (!bFinded)
                        {
                            ls_item += ReplaceEnter(row[i].ToString()) + "\t";
                        }
                    }
                }
                ls_item += "\n";
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();
        }
        /// <summary>
        /// 通用excel报表生成函数,新更正了只输出可视列:Donghongt 2006-6-14
        /// </summary>
        /// <param name="dtData">输出的数据源DataTable</param>
        /// <param name="FileName">输出的文件名</param>
        /// <param name="Page">当前页对象</param>
        /// <param name="sArrDateCloumn">日期列的列名数组</param>
        /// <param name="intOutPutColumn">输出列的索引号数组</param>
        public void getExcelFileCommon(DataTable dtData, string FileName, System.Web.UI.Page Page, string[] sArrDateCloumn, int[] intOutPutColumn)
        {
            #region 固定代码
            HttpResponse resp = Page.Response;

            resp.Clear();
            resp.Buffer = true;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            //resp.AppendHeader("Content-Disposition", "attachment;filename=Temp.xls");
            resp.AddHeader("Content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName + ".xls", Encoding.UTF8));
            resp.ContentType = "application/ms-excel";
            string colHeaders = "", ls_item = "";


            //取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符 
            foreach (int i in intOutPutColumn)
                colHeaders += ReplaceEnter(dtData.Columns[i].Caption.ToString()) + "\t";
            colHeaders += "\n";

            //向HTTP输出流中写入取得的数据信息 
            resp.Write(colHeaders);
            #endregion

            //逐行处理数据 
            bool bFinded = false;
            foreach (DataRow row in dtData.Rows)
            {
                //在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n 
                foreach (int iColumn in intOutPutColumn)
                {
                    bFinded = false;
                    if (sArrDateCloumn.Length == 0)
                    {
                        ls_item += ReplaceEnter(row[iColumn].ToString()) + "\t";
                    }
                    else
                    {
                        foreach (string ss in sArrDateCloumn)//对传入的每个日期列名数组元素
                        {
                            if (dtData.Columns[iColumn].ColumnName.Trim().ToLower() == ss)
                            {
                                if (row[iColumn].ToString().Trim() != "")
                                {
                                    ls_item += " " + DateTime.Parse(ReplaceEnter(row[iColumn].ToString()).Trim()).ToString("yyyy-MM-dd") + "\t";
                                    bFinded = true;
                                    break;
                                }
                            }
                        }
                        if (!bFinded)
                        {
                            ls_item += ReplaceEnter(row[iColumn].ToString()) + "\t";
                        }
                    }
                }
                ls_item += "\n";
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据 
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();
        }
        //
        private string ReplaceEnter(string str)
        {
            string s;
            s = str;
            if (str.Length > 0)
            {


                s = s.Replace("\r", "");
                s = s.Replace("\t", "");
                s = s.Replace("\n", "");
                s = s.Replace("\"", "");

                s = s.Trim();

            }
            return s;
        }

        #region 根据Excel文件路径，获得所有Sheet的名字
        /// <summary>
        /// 根据Excel文件路径，获得所有Sheet的名字
        /// </summary>
        /// <param name="strFileSrc">Excel文件路径（服务器）</param>
        /// <returns>Sheet名字的数组列表</returns>
        public ArrayList GetSheetNames(string strFileSrc)
        {
            ArrayList alResult = new ArrayList();
            string strConn = string.Format(GetConnectionString(strFileSrc), strFileSrc);
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
            if (dtSheetName.Rows.Count > 0)
            {
                foreach (DataRow dr in dtSheetName.Rows)
                {
                    alResult.Add(dr["TABLE_NAME"].ToString());
                }
            }
            conn.Close();
            return alResult;
        }
        #endregion

        #region 获得Excel中某个Sheet的前几行内容
        /// <summary>
        /// 获得Excel中某个Sheet的前几行内容
        /// </summary>
        /// <param name="strFileSrc">Excel路径</param>
        /// <param name="strSheetName">Sheet名称</param>
        /// <param name="iCount">行数</param>
        /// <returns>返回一个DataTable</returns>
        public DataTable GetSheetData(string strFileSrc, string strSheetName)
        {
            DataTable dtResult = new DataTable();
            DataSet ds = new DataSet();
            string strConn = string.Format(GetConnectionString(strFileSrc), strFileSrc);
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            string strSql = string.Format("select * from [{0}]", strSheetName);
            OleDbDataAdapter myDa = new OleDbDataAdapter(strSql, conn);
            myDa.Fill(ds, "tabledata");
            dtResult = ds.Tables["tabledata"];
            conn.Close();
            return dtResult;
        }
        #endregion
        #region 根据Excel路径，获得连接字符串
        /// <summary>
        /// 根据Excel路径，获得连接字符串
        /// </summary>
        /// <param name="filesrc">Excel路径</param>
        /// <returns>连接字符串</returns>
        public string GetConnectionString(string filesrc)
        {
            string result = "";
            string ext = GetPostfixStr(filesrc);
            switch (ext)
            {
                case ("xls"):
                    {
                        result = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
                        break;
                    }
                case ("xlsx"):
                    {
                        result = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
                        break;
                    }
            }
            return result;
        }
        #endregion
        #region 取得文件后缀名
        /****************************************
         * 函数名称：GetPostfixStr
         * 功能说明：取得文件后缀名
         * 参    数：filename:文件名称
         * 调用示列：
         *           string filename = "aaa.aspx";        
         *           string s = SINOBASELIB.FileObj.FileEdit.GetPostfixStr(filename);         
        *****************************************/
        /// <summary>
        /// 取后缀名
        /// </summary>
        /// <param name="filename">文件名</param>
        /// <returns>.gif|.html格式</returns>
        public static string GetPostfixStr(string filename)
        {
            int start = filename.LastIndexOf(".") + 1;
            int length = filename.Length;
            string postfix = filename.Substring(start, length - start);
            return postfix;
        }
        #endregion
       
    }
}
